package com.laivi.basic.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.laivi.basic.common.constants.Constant;
import com.laivi.basic.model.datastruct.ValueMap;
import com.laivi.basic.model.sql.SqlResult;

public class SqlUtil {
	
	public synchronized static SqlResult mapToInsert(String table,ValueMap value){
		if(value.size()==0) return null;
		List<ValueMap> values=new ArrayList<ValueMap>();
		values.add(value);
		return SqlUtil.mapToInsert(table,values);
	}
	
	public synchronized static SqlResult mapToInsert(String table,List<ValueMap> values){
		if(values.size()==0) return null;
		SqlResult result=new SqlResult();
		
		StringBuffer sb=new StringBuffer("insert into "+table+" (");
		StringBuffer valueSb=new StringBuffer("values (");
		List<List<Object>> valueLists=new ArrayList<List<Object>>();
		List<String> fields=new ArrayList<String>();
		
		for(Map.Entry<String, Object>entry:values.get(0).entrySet()){
			if(!"id".equals(entry.getKey()) && !"rownum".equals(entry.getKey())){
				sb.append(entry.getKey()+",");
				fields.add(entry.getKey());
				valueSb.append("?,");
			}
		}
		
		for(ValueMap map:values){
			List<Object> valueList=new ArrayList<Object>();
			for(String field:fields){
				valueList.add(map.get(field));
			}
			valueLists.add(valueList);
		}
		
		sb=new StringBuffer(sb.substring(0, sb.length()-1));
		valueSb=new StringBuffer(valueSb.substring(0, valueSb.length()-1));
		sb.append(") ");
		valueSb.append(")");
		sb.append(valueSb);
		result.setSql(sb.toString());
		result.setValues(valueLists);
		return result;
	}
	
	public synchronized static SqlResult mapToUpdate(String table,ValueMap value,String where){
		if(value.size()==0) return null;
		SqlResult result=new SqlResult();
		StringBuffer sb=new StringBuffer("update "+table+" set ");
		List<List<Object>> values=new ArrayList<List<Object>>();
		List<Object> valueList=new ArrayList<Object>();
		for(Map.Entry<String, Object>entry:value.entrySet()){
			sb.append(entry.getKey()+"=?,");
			valueList.add(entry.getValue());
		}
		values.add(valueList);
		sb=new StringBuffer(sb.substring(0, sb.length()-1));
		if(!DataUtil.isEmpty(where)){
			sb.append(" where "+where);
		}
		result.setSql(sb.toString());
		result.setValues(values);
		return result;
	}
	
	public synchronized static SqlResult mapToUpdate(String table,List<ValueMap> values,List<Map<String,Object>> wheres){
		if(values.size()==0) return null;
		SqlResult result=new SqlResult();
		StringBuffer sb=new StringBuffer("update "+table+" set ");
		List<List<Object>> valueLists=new ArrayList<List<Object>>();
		List<String> fields=new ArrayList<String>();
		int valueSize=0;
		for(Map.Entry<String, Object>entry:values.get(0).entrySet()){
			sb.append(entry.getKey()+"=?,");
			fields.add(entry.getKey());
			valueSize++;
		}
		sb=new StringBuffer(sb.substring(0, sb.length()-1));
		if(wheres.size()>0){
			StringBuffer where=new StringBuffer();
			for(Map.Entry<String, Object>entry:wheres.get(0).entrySet()){
				if(!DataUtil.isNull(entry.getValue())){
					where.append(entry.getKey()+" = ? and ");
					fields.add(entry.getKey());
				}
			}
			sb.append(" where "+where.substring(0, where.length()-4));
		}
		int n=0; 
		for(ValueMap map:values){
			List<Object> valueList=new ArrayList<Object>();
			int i=0;
			for(String field:fields){
				if(i>=valueSize){
					valueList.add(wheres.get(n).get(field));
				}else{
					valueList.add(map.get(field));
				}
			}
			valueLists.add(valueList);
			n++;
		}
		
		result.setSql(sb.toString());
		result.setValues(valueLists);
		return result;
	}
	
	public synchronized static SqlResult mapToUpdate(String table,List<ValueMap> values,String...whereFields){
		if(values.size()==0) return null;
		SqlResult result=new SqlResult();
		StringBuffer sb=new StringBuffer("update "+table+" set ");
		List<List<Object>> valueLists=new ArrayList<List<Object>>();
		List<String> fields=new ArrayList<String>();
		for(Map.Entry<String, Object>entry:values.get(0).entrySet()){
			sb.append(entry.getKey()+"=?,");
			fields.add(entry.getKey());
		}
		if(whereFields!=null && whereFields.length>0){
			StringBuffer where=new StringBuffer();
			for(String field:whereFields){
				where.append(field+"=? and ");
				fields.add(field);
			}
			sb.append(" where "+where.substring(0, where.length()-4));
		}
		
		for(ValueMap map:values){
			List<Object> valueList=new ArrayList<Object>();
			for(String field:fields){
				valueList.add(map.get(field));
			}
			valueLists.add(valueList);
		}
		
		result.setSql(sb.toString());
		result.setValues(valueLists);
		return result;
	}
	
	public synchronized static PreparedStatement prepareStatement(Connection conn,SqlResult sql,boolean returnKey) throws SQLException{
		PreparedStatement pst=null;
		if(returnKey && sql.getValues().size()==1){
			pst = conn.prepareStatement(sql.getSql(),Statement.RETURN_GENERATED_KEYS);
		}else{
			pst = conn.prepareStatement(sql.getSql());
		}
		int i;
		for(List<Object> objs:sql.getValues()){
			i=1;
			for(Object obj:objs){
				pst.setObject(i++, obj);
			}
			pst.addBatch();
		}
		return pst;
	}
	
	public synchronized static String mapToWhere(Map<String,Object> whereMap,boolean isLike){
		StringBuffer sb=new StringBuffer();
		for(Map.Entry<String, Object>entry:whereMap.entrySet()){
			if(!DataUtil.isNull(entry.getValue())){
				if(isLike){
					sb.append(entry.getKey()+" like ? and ");
				}else{
					sb.append(entry.getKey()+" = ? and ");
				}
			}
		}
		if(sb.length()==0){
			return null;
		}
		return sb.substring(0, sb.length()-4);
	}
	
	public synchronized static String mapToWhere(Map<String,Object> whereMap){
		return SqlUtil.mapToWhere(whereMap, false);
	}
	
	public synchronized static String toIn(List<ValueMap> fieldMap,String field){
		StringBuffer sb=new StringBuffer(" in (");
		for(ValueMap value:fieldMap){
			Object obj=value.get(field);
			if(obj instanceof String){
				sb.append("'"+String.valueOf(obj)+"',");
			}else{
				sb.append(String.valueOf(obj)+",");
			}
		}
		if(fieldMap!=null && fieldMap.size()>0){
			sb=new StringBuffer(sb.substring(0, sb.length()-1));
			sb.append(")");
		}else{
			return Constant.EMPTY;
		}
		return sb.toString();
	}

}
